package ws.jonas.hoarder.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import ws.jonas.common.log.Logger;
import ws.jonas.common.model.Store;

/**
 * Default statements are for MySQL.
 */
public abstract class SqlDb {
	private static final Logger log = Logger.getInstance(SqlDb.class);

	protected PreparedStatement selectProductIdPs;
	protected PreparedStatement selectStoreIdPs;
	protected PreparedStatement insertProductPs;
	protected PreparedStatement insertStorePs;
	protected PreparedStatement insertStockKnownStoreIdPs;
	protected PreparedStatement insertStockPs;
	protected Connection conn;

	private final Map<Store, Integer> knownStoreIds = new HashMap<Store, Integer>();

	/**
	 * @return true if any modification was done to the database.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addProductIfNotExists(int prodnr, String name, String volume) throws SQLException {
		int index = 0;
		selectProductIdPs.setInt(++index, prodnr);
		selectProductIdPs.setString(++index, volume);
		ResultSet selectRs = selectProductIdPs.executeQuery();
		if (selectRs.next()) {
			return false;
		} else {
			insertProduct(prodnr, name, volume);
			return true;
		}
	}

	/**
	 * @return true if any modification was done to the database.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStoreIfNotExists(Store store) throws SQLException {
		Integer id = knownStoreIds.get(store);
		if (id != null) {
			return false;
		}
		int index = 0;
		selectStoreIdPs.setString(++index, store.getCity());
		selectStoreIdPs.setString(++index, store.getAddress());
		selectStoreIdPs.setString(++index, store.getCounty());
		ResultSet selectRs = selectStoreIdPs.executeQuery();
		if (selectRs.next()) {
			id = selectRs.getInt("id");
			knownStoreIds.put(store, id);
			return false;
		} else {
			insertStore(store);
			return true;
		}
	}

	/**
	 * @return true.
	 * @throws SQLException if any SQL error occurred.
	 */
	public boolean addStock(int prodnr, String name, String volume, int qty, Store store) throws SQLException {
		insertStock(prodnr, volume, qty, store);
		return true;
	}

	protected void insertProduct(int prodnr, String name, String volume) throws SQLException {
		int index = 0;
		insertProductPs.setInt(++index, prodnr);
		insertProductPs.setString(++index, volume);
		insertProductPs.setString(++index, name);
		insertProductPs.executeUpdate();
	}

	protected void insertStore(Store store) throws SQLException {
		int index = 0;
		insertStorePs.setString(++index, store.getCity());
		insertStorePs.setString(++index, store.getAddress());
		insertStorePs.setString(++index, store.getCounty());
		insertStorePs.executeUpdate();
	}

	protected void insertStock(int prodnr, String volume, int quantity, Store store) throws SQLException {
		Integer id = knownStoreIds.get(store);
		if (id != null) {
			insertStock(prodnr, volume, quantity, id);
		} else {
			int index = 0;
			insertStockPs.setInt(++index, prodnr);
			insertStockPs.setString(++index, volume);
			insertStockPs.setString(++index, store.getCity());
			insertStockPs.setString(++index, store.getAddress());
			insertStockPs.setString(++index, store.getCounty());
			insertStockPs.setInt(++index, quantity);
			insertStockPs.executeUpdate();
		}
	}

	protected void insertStock(int prodnr, String volume, int quantity, int storeId) throws SQLException {
		int index = 0;
		insertStockKnownStoreIdPs.setInt(++index, prodnr);
		insertStockKnownStoreIdPs.setString(++index, volume);
		insertStockKnownStoreIdPs.setInt(++index, storeId);
		insertStockKnownStoreIdPs.setInt(++index, quantity);
		insertStockKnownStoreIdPs.executeUpdate();
	}

	//
	// SQL STATEMENTS
	//

	protected String getSelectProductIdStmt() {
		return "SELECT id FROM products WHERE prod_nr = ? AND volume = ?";
	}

	protected String getSelectStoreIdStmt() {
		return "SELECT id FROM stores WHERE city = ? AND address = ? AND county = ?";
	}

	protected String getInsertProductStmt() {
		return "INSERT INTO products(prod_nr, volume, name) VALUES(?, ?, ?)";
	}

	protected String getInsertStockStmt() {
		return "INSERT INTO stocks(prod_id, store_id, quantity, insert_timestamp) " +
				"VALUES((SELECT id FROM products WHERE prod_nr = ? AND volume = ?), (SELECT id FROM stores WHERE city = ? AND address = ? and county = ?), ?, NOW())";
	}

	protected String getInsertStockKnownStoreIdStmt() {
		return "INSERT INTO stocks(prod_id, store_id, quantity, insert_timestamp) " +
			"VALUES((SELECT id FROM products WHERE prod_nr = ? AND volume = ?), ?, ?, NOW())";
	}

	protected String getInsertStoreStmt() {
		return "INSERT INTO stores(city, address, county) VALUES(?, ?, ?)";
	}

	protected void prepareStatements() throws SQLException {
		try {
			selectProductIdPs = conn.prepareStatement(getSelectProductIdStmt());
			selectStoreIdPs = conn.prepareStatement(getSelectStoreIdStmt());
			insertProductPs = conn.prepareStatement(getInsertProductStmt());
			insertStockPs = conn.prepareStatement(getInsertStockStmt());
			insertStockKnownStoreIdPs = conn.prepareStatement(getInsertStockKnownStoreIdStmt());
			insertStorePs = conn.prepareStatement(getInsertStoreStmt());
		} catch (SQLException e) {
			log.severe("Could not set up database queries, shutting down");
			log.severe(e);
			throw(e);
		}
	}
}
